package database_interaction;

import java.sql.*;
import java.util.InputMismatchException;

/**
 * @file Database.java
 * @version 0.1
 * @author James Dibble 10009689
 */
public class Database {
    // <editor-fold defaultstate="collapsed" desc="Attributes">
    private String _serverURL;
    private Connection _sqlConnection;
    // </editor-fold>

    // <editor-fold defaultstate="collapsed" desc="Constructors">
    public Database(String serverURL) throws ClassNotFoundException, InstantiationException, IllegalAccessException {
        this._serverURL = serverURL;
        Class.forName ("com.mysql.jdbc.Driver").newInstance();
    }
    // </editor-fold>

    // <editor-fold defaultstate="collapsed" desc="Methods">
    /**
     * Connect a database using the given parameters.
     * 
     * @param strUser
     * @param strPassword
     * @throws SQLException 
     */
    public void connect(String strUser, String strPassword) throws SQLException{
        _sqlConnection = DriverManager.getConnection(_serverURL, strUser, strPassword);
    }
    
    /**
     * Create an insert statement for a given set of columns.
     * 
     * @param strTable
     * @param strColumnsArr
     * @param strValuesArr 
     */
    public void executeInsertStatement(String strTable, 
                                       String[] strColumnsArr, 
                                       Object[] objValuesArr) throws InputMismatchException, SQLException, Exception{
        
        if(strColumnsArr.length != objValuesArr.length) 
            throw new Exception("Columns array is not the same length as values array.");
        
        String strTempCols = "";
        for(String strThis: strColumnsArr){
            strTempCols += strThis + ",";
        }
        strTempCols = strTempCols.substring(0, strTempCols.length() - 1);
        
        String strTempVals = "";
        for(Object objThis: objValuesArr){
            if(objThis instanceof String){
                strTempVals += "'" + objThis + "',";
            }
            else if(objThis instanceof Integer){
                strTempVals += objThis + ",";
            }
            else{
                throw new InputMismatchException("Incorrect object type passed.  Must be String or Integer not int.");
            }
        }
        strTempVals = strTempVals.substring(0, strTempVals.length() - 1);
        
        String strQuery = String.format("INSERT INTO %s(%s) VALUES (%s)",
                                        strTable,
                                        strTempCols,
                                        strTempVals);
        executeQuery(strQuery);
    }
    
    /**
     * Create an insert statement for all columns.
     * 
     * @param strTable
     * @param strValuesArr 
     */
    public void executeInsertStatement(String strTable,  
                                       Object[] objValuesArr) throws InputMismatchException, SQLException{       
        String strTempVals = "";
        for(Object objThis: objValuesArr){
            if(objThis instanceof String){
                strTempVals += "'" + objThis + "',";
            }
            else if(objThis instanceof Integer){
                strTempVals += objThis + ",";
            }
            else if(objThis instanceof SQLDateTime){
                strTempVals += ((SQLDateTime)objThis).toString() + ",";
            }
            else{
                throw new InputMismatchException("Incorrect object type passed.  Must be String, Date or Integer not int.");
            }
        }
        strTempVals = strTempVals.substring(0, strTempVals.length() - 1);
        
        String strQuery = String.format("INSERT INTO %s VALUES (%s)",
                                        strTable,
                                        strTempVals);
        executeQuery(strQuery);
    }
    
   /**
    * Select all data from a table.
    * 
    * @param strTable
    * @return A result set of the data.
    * @throws SQLException 
    */
    public ResultSet executeSelectQuery(String strTable) throws SQLException{
        Statement thisStatement = _sqlConnection.createStatement();
        return thisStatement.executeQuery(String.format("SELECT * FROM %s",
                                          strTable));
    }
    
    /**
     * Select all columns from a database with a WHERE clause.
     * 
     * @param strTable
     * @param strFilter Example "name = 'john' AND age = 3"
     * @return
     * @throws SQLException 
     */
    public ResultSet executeSelectQuery(String strTable, String strFilter) throws SQLException{
        Statement thisStatement = _sqlConnection.createStatement();
        return thisStatement.executeQuery(String.format("SELECT * FROM %s WHERE %s",
                                          strTable,
                                          strFilter));
    }
    
    /**
     * Select certain columns from a table
     * 
     * @param strTable
     * @param strArrColumns
     * @return
     * @throws SQLException 
     */
    public ResultSet executeSelectQuery(String strTable, String[] strArrColumns) throws SQLException{
        Statement thisStatement = _sqlConnection.createStatement();
        String strColumns = "";
        for(String strThisColumn: strArrColumns){
            strColumns += strThisColumn + ",";
        }
        strColumns = strColumns.substring(0, strColumns.length() - 1);
        return thisStatement.executeQuery(String.format("SELECT %s FROM %s",
                                          strColumns,
                                          strTable));
    }
    
    /**
     * Select columns from a table with a WHERE clause.
     * 
     * @param strTable
     * @param strArrColumns
     * @param strFilter Example "name = 'john' AND age = 3"
     * @return
     * @throws SQLException 
     */
    public ResultSet executeSelectQuery(String strTable, String[] strArrColumns, String strFilter) throws SQLException{
        Statement thisStatement = _sqlConnection.createStatement();
        String strColumns = "";
        for(String strThisColumn: strArrColumns){
            strColumns += strThisColumn + ",";
        }
        strColumns = strColumns.substring(0, strColumns.length() - 1);
        return thisStatement.executeQuery(String.format("SELECT %s FROM %s WHERE %s",
                                          strColumns,
                                          strTable,
                                          strFilter));
    }
    
    /**
     * Update a row with the given ID. <br /><br />
     * Column index in array must coincide with object index.
     * 
     * @param strTable
     * @param strColumnsArr
     * @param objValuesArr
     * @param ID
     * @throws SQLException
     * @throws InputMismatchException
     * @throws Exception 
     */
    public void executeUpdateQuery(String strTable, String[] strColumnsArr, Object[] objValuesArr, int ID) throws SQLException, InputMismatchException, Exception{
        if(strColumnsArr.length != objValuesArr.length) 
            throw new Exception("Columns array is not the same length as values array.");
        
        String strTempVals = "";
        int intCount = 0;
        for(Object objThis: objValuesArr){
            if(objThis instanceof String){
                strTempVals += String.format("%s='%s',", 
                                             strColumnsArr[intCount],
                                             objThis);
            }
            else if(objThis instanceof Integer){
                strTempVals += String.format("%s=%s,", 
                                             strColumnsArr[intCount],
                                             objThis);
            }
            else if(objThis instanceof SQLDateTime){
                strTempVals += String.format("%s=%s,", 
                                             strColumnsArr[intCount],
                                             ((SQLDateTime)objThis).toString());
            }
            else{
                throw new InputMismatchException("Incorrect object type passed.  Must be String or Integer not int.");
            }
            intCount++;
        }
        strTempVals = strTempVals.substring(0, strTempVals.length() - 1);
        
        String strQuery = String.format("UPDATE %s SET %s WHERE id=%d",
                                        strTable,
                                        strTempVals,
                                        ID);
        executeQuery(strQuery);
    }
    
    /**
     * Update a row with a filter. <br /><br />
     * Column index in array must coincide with object index.
     * 
     * @param strTable
     * @param strColumnsArr
     * @param objValuesArr
     * @param strFilter The WHERE clause.
     * @throws SQLException
     * @throws InputMismatchException
     * @throws Exception 
     */
    public void executeUpdateQuery(String strTable, String[] strColumnsArr, Object[] objValuesArr, String strFilter) throws SQLException, InputMismatchException, Exception{
        if(strColumnsArr.length != objValuesArr.length) 
            throw new Exception("Columns array is not the same length as values array.");
        
        String strTempVals = "";
        int intCount = 0;
        for(Object objThis: objValuesArr){
            if(objThis instanceof String){
                strTempVals += String.format("%s='%s',", 
                                             strColumnsArr[intCount],
                                             objThis);
            }
            else if(objThis instanceof Integer){
                strTempVals += String.format("%s=%s,", 
                                             strColumnsArr[intCount],
                                             objThis);
            }
            else if(objThis instanceof SQLDateTime){
                strTempVals += String.format("%s=%s,", 
                                             strColumnsArr[intCount],
                                             ((SQLDateTime)objThis).toString());
            }
            else{
                throw new InputMismatchException("Incorrect object type passed.  Must be String or Integer not int.");
            }
            intCount++;
        }
        strTempVals = strTempVals.substring(0, strTempVals.length() - 1);
        
        String strQuery = String.format("UPDATE %s SET %s WHERE %s",
                                        strTable,
                                        strTempVals,
                                        strFilter);
        executeQuery(strQuery);
    }
    
    /**
     * Delete a row from the database using a filter.
     * 
     * @param strTable
     * @param strFilter The WHERE clause.
     * @throws SQLException 
     */
    public void executeDeleteQuery(String strTable, String strFilter) throws SQLException{
        String strQuery = String.format("DELETE FROM %s WHERE id=%s",
                                        strTable,
                                        strFilter);
        executeQuery(strQuery);
    }
    
    /**
     * Delete a row from the database where a column named "id".
     * 
     * @param strTable
     * @param ID The "id" of the row.
     * @throws SQLException 
     */
    public void executeDeleteQuery(String strTable, int ID) throws SQLException{
        String strQuery = String.format("DELETE FROM %s WHERE id=%d",
                                        strTable,
                                        ID);
        executeQuery(strQuery);
    }
    
    private void executeQuery(String strQuery) throws SQLException{
        Statement thisStatement = _sqlConnection.createStatement();
        thisStatement.executeUpdate(strQuery);
    }
    // </editor-fold>
}
